﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using System.Text.RegularExpressions;

namespace mpsalary
{
    public partial class AdminUploadResult : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void imagebtnHome0_Click(object sender, ImageClickEventArgs e)
        {
            Response.Redirect("homePage.aspx");
        }


        protected void ButtonUploadFile_Click(object sender, EventArgs e)
        {
            

            if (uploadFileTextbox.HasFile)
            {
                try
                {
                    // alter path for project

                    uploadFileTextbox.SaveAs(Server.MapPath("~/Saved.xls"));
                    LabelUpload.Text = "Upload File Name: " + 
                     uploadFileTextbox.PostedFile.FileName + "<br>" + "Type: " +
                    uploadFileTextbox.PostedFile.ContentType +
                    " File Size: " + uploadFileTextbox.PostedFile.ContentLength
                    + " kb<br>";
                }
                catch (System.NullReferenceException ex)
                {
                    LabelUpload.Text = "Error: " + ex.Message;
                }
            }
            else
            {
                LabelUpload.Text = "Please select a file to upload.";
            }

            //btnviewData.Visible = true;
        }

        //Upload File Button
        protected void Button1_Click(object sender, EventArgs e)
        {
            PanelUpload.Visible = true;
            PanelView.Visible = false;
            PanelImport.Visible = false;


            LabelUpload.Text = "";
        }
        protected OleDbCommand ExcelConnection()
        {
            

            // Connect to the Excel Spreadsheet
            string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + Server.MapPath("Saved.xls") + ";" +
            "Extended Properties=Excel 8.0;";


            // create your excel connection object using the connection string
            OleDbConnection Conn = new OleDbConnection(connectionstring);
            Conn.Open();

            

            // use a SQL Select command to retrieve the data from the Excel Spreadsheet
            // the "table name" is the name of the worksheet within the spreadsheet
            // in this case, the worksheet name is "2010" and is coded as: [2010$]
           
            OleDbCommand Comm = new OleDbCommand("SELECT * FROM [2010$]", Conn);
            return Comm;

        }

        //View Data Button
        protected void Button2_Click(object sender, EventArgs e)
        {
            PanelUpload.Visible = false;
            PanelView.Visible = true;
            PanelImport.Visible = false;
            
            //array that store the worksheet name
            String[] excelsheetname = GetExcelSheetNames(Server.MapPath("~/Saved.xls"));

            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath("~/Saved.xls") + @";Extended Properties=""Excel 12.0; HDR=YES; IMEX=1; ImportMixedTypes=Text; TypeGuessRows=0;""";

            //to retrieve data from the excel using select query
            OleDbCommand command = new OleDbCommand("SELECT * FROM [" + excelsheetname[0].ToString() + "]", conn);

            DataSet objDataSet = new DataSet();

            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
            objDataAdapter.SelectCommand = command;

            objDataAdapter.Fill(objDataSet);

            GridViewExcel.DataSource = objDataSet.Tables[0].DefaultView;
            GridViewExcel.DataBind();

            //// Create a new Adapter
            //OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();

            //// retrieve the Select command for the Spreadsheet
            //objDataAdapter.SelectCommand = ExcelConnection();

            //// Create a DataSet
            //DataSet objDataSet = new DataSet();
            //// Populate the DataSet with the spreadsheet worksheet data
            //objDataAdapter.Fill(objDataSet);

            //// Bind the data to the GridView
            //GridViewExcel.DataSource = objDataSet.Tables[0].DefaultView;
            //GridViewExcel.DataBind();

        }
        
        public static String[] GetExcelSheetNames(string excelFile)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            try
            {
                // Connection String. Change the excel file to the file you import.

                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
                // Create connection object by using the preceding connection string.

                objConn = new OleDbConnection(connString);
                // Open connection with the database.

                objConn.Open();
                // Get the data table containg the schema guid.

                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                 
                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.

                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }
                
                return excelSheets;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                // Clean up.

                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
        public static string RemoveSpecialCharacters(string[] input)
        {
            Regex r = new Regex("(?:[^a-z0-9 ]|(?<=['\"])s)", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.Compiled);
            return r.Replace(input[0], String.Empty);
        }
        protected void btnImportData_Click(object sender, EventArgs e)
        {
            
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                conn.Open();

                comm.Connection = conn;
                
                string Course = "";
                string[] year = GetExcelSheetNames(Server.MapPath("~/Saved.xls"));
                string t = RemoveSpecialCharacters(year);

                //string[] lines = Regex.Split(year[], "$");
                //Console.WriteLine(lines);
                //string [] split = year[0].Split(new Char [] {' ','$'});
                //split[0].Substring(0,3);
               // string yearint = "";
               /* foreach (string s in split)
                {
                    if (s.Trim() != "")
                    {
                        yearint = s.ToString();

                        //Console.WriteLine(s);
                    }
                }*/

                /*for (int i = 0; i < split.Length; i++) 
                {
                    yearint = i.ToString();
                    
                }

                Console.WriteLine(yearint);*/
                string PolyConducting = "";
                string FreshGradsEmpNum = "";
                string FreshGradsEmpRate = "";
                string FreshGradsMonthlyMean = "";
                string FreshGradsMonthlyMedian = "";
                string PostGradsEmpNum = "";
                string PostGradsEmpRate = "";
                string PostGradsMonthlyMean = "";
                string PostGradsMonthlyMedian = "";

                int count = GridViewExcel.Rows.Count;

                foreach (GridViewRow gvr in GridViewExcel.Rows)
                {
                    
                    Course = gvr.Cells[0].Text;
                    PolyConducting = gvr.Cells[1].Text;
                    FreshGradsEmpNum = gvr.Cells[2].Text;
                    FreshGradsEmpRate = gvr.Cells[3].Text;
                    FreshGradsMonthlyMean = gvr.Cells[4].Text;
                    FreshGradsMonthlyMedian = gvr.Cells[5].Text;
                    PostGradsEmpNum = gvr.Cells[6].Text;
                    PostGradsEmpRate = gvr.Cells[7].Text;
                    PostGradsMonthlyMean = gvr.Cells[8].Text;
                    PostGradsMonthlyMedian = gvr.Cells[9].Text;
                    
                    //comm.CommandText = "INSERT INTO 2010 (Course, PolyConducting, FreshGradsEmpNum, FreshGradsEmpRate, FreshGradsMonthlyMean, FreshGradsMonthlyMedian, PostNSEmpNum, PostNSEmpRate,PostNSMonthlyMean, PostNSMonthlyMedian) VALUES (@Course, @PolyConducting,@FreshGradsEmpNum, @FreshGradsEmpRate, @FreshGradsMonthlyMean, @FreshGradsMonthlyMedian, @PostNSEmpNum, @PostNSEmpRate, @PostNSMonthlyMean, @PostNSMonthlyMedian)";
                    comm.CommandText = " INSERT INTO Wages(Course, year, PolyConducting, FreshGradsEmpNum, FreshGradsEmpRate, FreshGradsMonthlyMean, FreshGradsMonthlyMedian, PostGradsEmpNum, PostGradsEmpRate, PostGradsMonthlyMean, PostGradsMonthlyMedian) VALUES (@Course, @Year, @PolyConducting, @FreshGradsEmpNum, @FreshGradsEmpRate, @FreshGradsMonthlyMean, @FreshGradsMonthlyMedian, @PostGradsEmpNum, @PostGradsEmpRate, @PostGradsMonthlyMean, @PostGradsMonthlyMedian)";
                    comm.Parameters.AddWithValue("@Course", Course);
                    comm.Parameters.AddWithValue("@Year", t);
                    comm.Parameters.AddWithValue("@PolyConducting", PolyConducting);
                    comm.Parameters.AddWithValue("@FreshGradsEmpNum", FreshGradsEmpNum);
                    comm.Parameters.AddWithValue("@FreshGradsEmpRate", FreshGradsEmpRate);
                    comm.Parameters.AddWithValue("@FreshGradsMonthlyMean", FreshGradsMonthlyMean);
                    comm.Parameters.AddWithValue("@FreshGradsMonthlyMedian", FreshGradsMonthlyMedian);
                    comm.Parameters.AddWithValue("@PostGradsEmpNum", PostGradsEmpNum);
                    comm.Parameters.AddWithValue("@PostGradsEmpRate", PostGradsEmpRate);
                    comm.Parameters.AddWithValue("@PostGradsMonthlyMean", PostGradsMonthlyMean);
                    comm.Parameters.AddWithValue("@PostGradsMonthlyMedian", PostGradsMonthlyMedian);
                    comm.ExecuteNonQuery();



                    
                    comm.Parameters.Clear();
                    Debug.WriteLine("Sql Command Completed");
                }
            }

            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }


        }
        



    }
}